package com.acme.chat.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.acme.chat.message.PrivateMessage;
import com.acme.chat.util.Utils;

public final class DbAdapter {

    private static final String DB_URL = "jdbc:derby://localhost:1527/JET_0002_TEAM04;create=true";
    private static final String DB_DRIVER = "org.apache.derby.jdbc.ClientDriver";

    private static final String ERROR_CANT_SAVE_MESSAGE = "Can't save private message!";

    private static final String SCHEMA = "JET_0002_TEAM04";
    private static final String TBL_CLIENT = "CLIENT";
    private static final String TBL_HISTORY = "HISTORY";
    private static final String TBL_CLIENT_FULL = SCHEMA + "." + TBL_CLIENT;
    private static final String TBL_HISTORY_FULL = SCHEMA + "." + TBL_HISTORY;

    private static final int PARAM_1 = 1;
    private static final int PARAM_2 = 2;
    private static final int PARAM_3 = 3;
    private static final int PARAM_4 = 4;

    private static DbAdapter dbAdapter = new DbAdapter();
    private static Logger dbLogger = Logger.getLogger(DbAdapter.class.getName());

    private Connection connection;

    private DbAdapter() {

    }

    private Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DB_DRIVER).newInstance();
            conn = DriverManager.getConnection(DB_URL);
        } catch (InstantiationException e) {
            dbLogger.info(e.getMessage());
        } catch (IllegalAccessException e) {
            dbLogger.info(e.getMessage());
        } catch (ClassNotFoundException e) {
            dbLogger.info(e.getMessage());
        } catch (SQLException e) {
            dbLogger.info(e.getMessage());
        }
        return conn;
    }

    /**
     * Method drop all data from JET_0002_TEAM04 schema and create <br>
     * tables CLIENT and HISTORY.
     */
    public void prepareDb() {
        // drop data before creation, if exists
        if (connection == null) {
            return;
        }
        dropData();
        createSchema();
        populateData();
    }

    /**
     * Try connect to the DB.
     *
     * @throws Exception
     */
    public void connect() {
        connection = getConnection();
    }

    private void createSchema() {
        execUpdate("CREATE TABLE " + TBL_CLIENT_FULL + " ("
                    + "ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                    + "NAME VARCHAR(128) NOT NULL, "
                    + "CONSTRAINT PK_CLIENT PRIMARY KEY (ID))");
        execUpdate("CREATE TABLE " + TBL_HISTORY_FULL + " ("
                    + "ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                    + "FROM_ID INTEGER NOT NULL, "
                    + "CONSTRAINT FK_FROM FOREIGN KEY (FROM_ID) REFERENCES " + TBL_CLIENT_FULL + "(ID), "
                    + "TO_ID INTEGER NOT NULL, "
                    + "CONSTRAINT FR_TO FOREIGN KEY (TO_ID) REFERENCES " + TBL_CLIENT_FULL +"(ID), "
                    + "DATE_TIME TIMESTAMP NOT NULL, "
                    + "MESSAGE VARCHAR(1000) NOT NULL)");
    }

    private void populateData() {
        populateClient();
        populateMessage();
    }

    private void populateClient() {
        String[] clients = {"user1"};
        for (String client : clients) {
            insertClient(client);
        }
    }

    private void insertClient(String name) {
        String insert = "INSERT INTO " + TBL_CLIENT_FULL + " (NAME) VALUES(?)";
        PreparedStatement prepStatement = null;
        try {
            prepStatement = prepareStatement(insert);
            prepStatement.setString(PARAM_1, name);
            prepStatement.executeUpdate();
        } catch (SQLException e) {
            dbLogger.info(e.getMessage());
        }
    }

    private int selectClientId(String name) {
        int id = 0;
        String query = "SELECT ID FROM " + TBL_CLIENT_FULL + " WHERE NAME = ?";
        PreparedStatement prepStatement = null;
        ResultSet rs = null;
        try {
            prepStatement = prepareStatement(query);
            prepStatement.setString(PARAM_1, name);
            rs = prepStatement.executeQuery();
            if (rs.next()) {
                id = rs.getInt("ID");
            }
        } catch (SQLException e) {
            dbLogger.info(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
            	dbLogger.info(e.getMessage());
            }
        }

        return id;
    }

    private int insertPrivateMessage(int fromId, int toId, Timestamp timeStamp, String message) {
        int rowCount = 0;
        String query = "INSERT INTO " + TBL_HISTORY_FULL + " (FROM_ID, TO_ID, DATE_TIME, MESSAGE) VALUES(?,?,?,?)";
        PreparedStatement prepStatement = null;
        try {
            prepStatement = prepareStatement(query);
            prepStatement.setInt(PARAM_1, fromId);
            prepStatement.setInt(PARAM_2, toId);
            prepStatement.setTimestamp(PARAM_3, timeStamp);
            prepStatement.setString(PARAM_4, message);
            rowCount = prepStatement.executeUpdate();
        } catch (SQLException e) {
            dbLogger.info(ERROR_CANT_SAVE_MESSAGE);
        	dbLogger.info(e.getMessage());
        }
        return rowCount;
    }

    private void populateMessage() {
        savePrivateMessage("user1", "user5", Utils.getCurrentTimestamp(), "Private message from user1 to user5.");
    }

    private int savePrivateMessage(String fromName, String toName, Timestamp currentTimeStamp, String message) {

        int fromId = getClientId(fromName);
        int toId = getClientId(toName);

        if (fromId == 0) {
            insertClient(fromName);
            fromId = getClientId(fromName);
        }

        if (toId == 0) {
            insertClient(toName);
            toId = getClientId(toName);
        }

        return insertPrivateMessage(fromId, toId, currentTimeStamp, message);
    }

    /**
     * Method save private message to the DB.
     *
     * @param message PrivateMessage instance
     */
    public void savePrivateMessage(PrivateMessage message) {
        savePrivateMessage(message.getFrom(), message.getTo(), message.getTimestamp(), message.getMessage());
    }

    private int getClientId(String name) {
        return selectClientId(name);
    }

    private String getClientName(int id) {
        return selectClientName(id);
    }

    private String selectClientName(int id) {
        String query = "SELECT NAME FROM " + TBL_CLIENT_FULL + " WHERE ID = ?";
        String name = null;
        PreparedStatement prepStatement = null;
        ResultSet rs = null;
        try {
            prepStatement = prepareStatement(query);
            prepStatement.setInt(PARAM_1, id);
            rs = prepStatement.executeQuery();
            if (rs.next()) {
                name = rs.getString("NAME");
            }
        } catch (SQLException e) {
        	dbLogger.info(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
            	dbLogger.info(e.getMessage());
            }
        }

        return name;
    }

    /**
     * Method returns list of private messages for the client.
     *
     * @param name clients name
     * @return
     */
    public List<String> getPrivateMessages(String name) {
        List<String> messages = new ArrayList<String>();
        ResultSet rs = null;

        try {
            rs = selectPrivateMessage(name);
            while (rs.next()) {
                int fromId = rs.getInt("FROM_ID");
                int toId = rs.getInt("TO_ID");
                Timestamp ts = rs.getTimestamp("DATE_TIME");
                String message = rs.getString("MESSAGE");
                messages.add(composeMessage(getClientName(fromId), getClientName(toId), ts, message));
            }
        } catch (SQLException e) {
        	dbLogger.info(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
            	dbLogger.info(e.getMessage());
            }
        }

        return messages;
    }

    private String composeMessage(String fromName, String toName, Timestamp ts, String message) {
        return ts + "> private from " + fromName + " to " + toName + ": " + message;
    }

    private ResultSet selectPrivateMessage(String name) throws SQLException {
        String query = "SELECT FROM_ID, TO_ID, DATE_TIME, MESSAGE FROM " + TBL_HISTORY_FULL + " WHERE FROM_ID = ? OR TO_ID = ?";
        int id = getClientId(name);
        PreparedStatement prepStatement = null;
        ResultSet rs = null;
        prepStatement = prepareStatement(query);
        prepStatement.setInt(PARAM_1, id);
        prepStatement.setInt(PARAM_2, id);
        rs = prepStatement.executeQuery();

        return rs;
    }

    private void execUpdate(String query) {
        PreparedStatement prepStatement = null;
        try {
            prepStatement = connection.prepareStatement(query);
            prepStatement.executeUpdate();
        } catch (SQLException e) {
        	dbLogger.info(e.getMessage());
        } finally {
            try {
                if (prepStatement != null) {
                    prepStatement.close();
                }
            } catch (SQLException e) {
            	dbLogger.info(e.getMessage());
            }
        }
    }

    private PreparedStatement prepareStatement(String query) throws SQLException {
        PreparedStatement prepStatement = null;
        prepStatement = connection.prepareStatement(query);
        return prepStatement;
    }

    private void dropData() {
        // only that deletion order, or need before delete foreign keys
        execUpdate("DROP TABLE " + TBL_HISTORY_FULL);
        execUpdate("DROP TABLE " + TBL_CLIENT_FULL);
    }

    /**
     * Method close connection to the DB.
     */
    public void close() {
        dropData();

        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
        	dbLogger.info(e.getMessage());
        }
    }

    /**
     * Return instance of the DbAdapter.
     *
     * @return instance of the DbAdapter
     */
    public static DbAdapter getInstance() {
        return dbAdapter;
    }
}
